import json
import os
import time

import numpy as np
import pandas as pd
from datetime import date, datetime
from operator import itemgetter
from urllib.parse import unquote

from django.db import connection
from django.http import HttpResponse, FileResponse

from Enums import *
from project_manage.models import *
from sanhangbei import delete


class ComplexEncoder(json.JSONEncoder):
    def default(self, obj):
        if isinstance(obj, datetime):
            return obj.strftime('%Y-%m-%d %H:%M:%S')
        elif isinstance(obj, date):
            return obj.strftime('%Y-%m-%d')
        else:
            return json.JSONEncoder.default(self, obj)


# Create your views here.

def addProject(request):
    if request.method != 'POST':
        return JsonResponseUtf8({}, 405, False, "方法不允许")
    session = request.session.get("info")
    if session is None:
        return JsonResponseUtf8({}, 404, False, "会话过期")
    # Student.objects.filter(sno="2020302635").update(name="sss")
    post = json.loads(request.body.decode("utf-8"))
    project = post["project"]
    members = post["members"]
    teachers = post["teachers"]
    year = post["year"]
    form_url = post["formUrl"]
    enclosure_url = post["enclosureUrl"]
    project["type"] = track2type(project["type"])
    # project["college"] = College.str2int(project["college"])
    try:
        p = Project(
            name=project["name"],
            main_track=project["type"][0],
            sub_track1=project["type"][1],
            sub_track2=project["type"][2],
            year=year,
            college=College.str2int(project['college']),
            brief=project['brief'],
            form_url=form_url,
            enclosure_url=enclosure_url,
        )
        p.save()
        for member in members:
            student_set = Student.objects.filter(sno=member['sno'])
            if student_set.first() is None:
                s = Student(
                    sno=member['sno'],
                    name=member['name'],
                    sex=Sex.str2int(member['sex']),
                    college=College.str2int(member['college']),
                    major=member['major'],
                    email=member['email'],
                    phone=member['phone'],
                    qq=member['qq'],
                    wechat=member['wechat']
                )
                s.save()
            else:
                s = student_set.first()
                student_sex = Sex.str2int(member['sex'])
                student_college = College.str2int(member['college'])
                student_set.update(
                    name=member['name'],
                    sex=student_sex if student_sex != 2 else s.sex,
                    college=student_college if student_college != 0 else s.college,
                    major=member['major'] if member['major'] != '' else s.major,
                    email=member['email'] if member['email'] != '' else s.email,
                    phone=member['phone'] if member['email'] != '' else s.phone,
                    qq=member['qq'] if member['email'] != '' else s.qq,
                    wechat=member['wechat'] if member['email'] != '' else s.wechat
                )
                # print(x)
            ps = ProjectStudent(
                pid=p,
                sno=Student.objects.filter(sno=member['sno']).first(),
                index=member['index'],
                division=member['division']
            )
            ps.save()
        for teacher in teachers:
            teacher_set = Teacher.objects.filter(tno=teacher['tno'])
            if teacher_set.first() is None:
                t = Teacher(
                    tno=teacher['tno'],
                    name=teacher['name'],
                    title=teacher['title'],
                    college=College.str2int(teacher['college']),
                    phone=teacher['phone'],
                    email=teacher['email']
                )
                t.save()
            else:
                t = teacher_set.first()
                teacher_college = College.str2int(teacher['college'])
                teacher_set.update(
                    name=teacher['name'],
                    title=teacher['title'] if teacher['title'] != '' else t.title,
                    college=teacher_college if teacher_college != 0 else t.college,
                    phone=teacher['phone'] if teacher['phone'] != '' else t.phone,
                    email=teacher['email'] if teacher['email'] != '' else t.email,
                )
            pt = ProjectTeacher(
                pid=p,
                tno=Teacher.objects.filter(tno=teacher['tno']).first(),
                index=teacher['index'],
            )
            pt.save()
    except Exception as e:
        return JsonResponseUtf8({}, 500, False, str(e))
    return JsonResponseUtf8({}, 200, True, "操作成功")


def editProject(request):
    if request.method != 'POST':
        return JsonResponseUtf8({}, 405, False, "方法不允许")
    session = request.session.get("info")
    if session is None:
        return JsonResponseUtf8({}, 404, False, "会话过期")
    # Student.objects.filter(sno="2020302635").update(name="sss")
    post = json.loads(request.body.decode("utf-8"))
    id = post['id']
    project = post["project"]
    members = post["members"]
    teachers = post["teachers"]
    year = post["year"]
    form_url = post["formUrl"]
    enclosure_url = post["enclosureUrl"]
    project["type"] = track2type(project["type"])
    # project["college"] = College.str2int(project["college"])
    try:
        p = Project(
            name=project["name"],
            main_track=project["type"][0],
            sub_track1=project["type"][1],
            sub_track2=project["type"][2],
            year=year,
            college=College.str2int(project['college']),
            brief=project['brief'],
            form_url=form_url,
            enclosure_url=enclosure_url,
        )
        p.save()
        newId = p.id
        for member in members:
            student_set = Student.objects.filter(sno=member['sno'])
            if student_set.first() is None:
                s = Student(
                    sno=member['sno'],
                    name=member['name'],
                    sex=Sex.str2int(member['sex']),
                    college=College.str2int(member['college']),
                    major=member['major'],
                    email=member['email'],
                    phone=member['phone'],
                    qq=member['qq'],
                    wechat=member['wechat']
                )
                s.save()
            else:
                s = student_set.first()
                student_sex = Sex.str2int(member['sex'])
                student_college = College.str2int(member['college'])
                student_set.update(
                    name=member['name'],
                    sex=student_sex if student_sex != 2 else s.sex,
                    college=student_college if student_college != 0 else s.college,
                    major=member['major'] if member['major'] != '' else s.major,
                    email=member['email'] if member['email'] != '' else s.email,
                    phone=member['phone'] if member['email'] != '' else s.phone,
                    qq=member['qq'] if member['email'] != '' else s.qq,
                    wechat=member['wechat'] if member['email'] != '' else s.wechat
                )
                # print(x)
            ps = ProjectStudent(
                pid=p,
                sno=Student.objects.filter(sno=member['sno']).first(),
                index=member['index'],
                division=member['division']
            )
            ps.save()
        for teacher in teachers:
            teacher_set = Teacher.objects.filter(tno=teacher['tno'])
            if teacher_set.first() is None:
                t = Teacher(
                    tno=teacher['tno'],
                    name=teacher['name'],
                    title=teacher['title'],
                    college=College.str2int(teacher['college']),
                    phone=teacher['phone'],
                    email=teacher['email']
                )
                t.save()
            else:
                t = teacher_set.first()
                teacher_college = College.str2int(teacher['college'])
                teacher_set.update(
                    name=teacher['name'],
                    title=teacher['title'] if teacher['title'] != '' else t.title,
                    college=teacher_college if teacher_college != 0 else t.college,
                    phone=teacher['phone'] if teacher['phone'] != '' else t.phone,
                    email=teacher['email'] if teacher['email'] != '' else t.email,
                )
            pt = ProjectTeacher(
                pid=p,
                tno=Teacher.objects.filter(tno=teacher['tno']).first(),
                index=teacher['index'],
            )
            pt.save()
        with connection.cursor() as cur:
            cur.execute(f'''
                select form_url, enclosure_url from project where id={id};
            ''')
            fa = cur.fetchall()[0]
            form_url = unquote(fa[0])
            enclosure_url = unquote(fa[1])
            delete(form_url)
            delete(enclosure_url)
            cur.execute(f'''
                delete from projectstudent where pid_id={id};
            ''')
            cur.execute(f'''
                delete from projectteacher where pid_id={id};
            ''')
            cur.execute(f'''
                delete from project where id={id};
            ''')
            formStudentIndex(cur)
            formTeacherIndex(cur)
        connection.commit()
    except Exception as e:
        return JsonResponseUtf8({}, 500, False, str(e))
    return JsonResponseUtf8({'id': newId}, 200, True, "操作成功")


def getProjects(request):
    session = request.session.get("info")
    if session is None:
        return JsonResponseUtf8({}, 404, False, "会话过期")
    try:
        project_list = Project.objects.raw('''
            select 
                project.id, project.name, project.main_track, project.sub_track1, project.sub_track2,
                project.year, project.college, project.form_url, project.enclosure_url,
                s.name as cname, sno as cid
            from 
                project join projectstudent p on project.id = p.pid_id join student s on p.sno_id = s.sno
            where 
                `index` = 0
            order by 
                project.name;
        ''')
        projects = []
        for p in project_list:
            projects.append({
                "id": p.id,
                "name": p.name,
                "main_track": MainTrack.int2str(p.main_track),
                "sub_track1": SubTrack1.int2str(p.sub_track1),
                "sub_track2": SubTrack2.int2str(p.sub_track2),
                "year": p.year,
                "college": College.int2str(p.college),
                "form_url": p.form_url,
                "enclosure_url": p.enclosure_url,
                "cname": p.cname,
                "cid": p.cid
            })
    except Exception as e:
        return JsonResponseUtf8({}, 500, False, str(e))
    return JsonResponseUtf8({"projects": projects}, 200, True, "操作成功")


def getProjectDetail(request):
    session = request.session.get("info")
    if session is None:
        return JsonResponseUtf8({}, 404, False, "会话过期")
    try:
        id = request.GET.get("id")
        data = {}
        project = Project.objects.raw(f'''
            select 
                project.id, project.name, project.main_track, project.sub_track1, project.sub_track2,
                project.year, project.college, project.brief, project.form_url, project.enclosure_url
            from 
                project
            where 
                `id` = {id}
        ''')[0]
        data["id"] = project.id
        data["name"] = project.name
        data["main_track"] = MainTrack.int2str(project.main_track)
        data["sub_track1"] = SubTrack1.int2str(project.sub_track1)
        data["sub_track2"] = SubTrack2.int2str(project.sub_track2)
        data["year"] = project.year
        data["college"] = College.int2str(project.college)
        data["brief"] = project.brief
        data["form_url"] = project.form_url
        data["enclosure_url"] = project.enclosure_url
        students_set = Student.objects.raw(f'''
            select 
                sno, student.name, sex, college, major, email, phone, qq, wechat, division, `index`
            from 
                student join projectstudent p on student.sno = p.sno_id
            where pid_id = {id};
        ''')
        students = []
        for s in students_set:
            students.append({
                "sno": s.sno,
                "name": s.name,
                "sex": Sex.int2str(s.sex),
                "college": College.int2str(s.college),
                "major": s.major,
                "email": s.email,
                "phone": s.phone,
                "qq": s.qq,
                "wechat": s.wechat,
                "division": s.division,
                "index": s.index
            })
        data['students'] = sorted(students, key=itemgetter('index'))
        teachers_set = Teacher.objects.raw(f'''
            select 
                tno, teacher.name, title, college, phone, email, `index`
            from 
                teacher join projectteacher p on teacher.tno = p.tno_id
            where pid_id = {id};
        ''')
        teachers = []
        for t in teachers_set:
            teachers.append({
                "tno": t.tno,
                "name": t.name,
                "title": t.title,
                "college": College.int2str(t.college),
                "email": t.email,
                "phone": t.phone,
                "index": t.index
            })
        data['teachers'] = sorted(teachers, key=itemgetter('index'))
        same_set = Project.objects.raw(f'''
            select 
                project.id, project.name, project.main_track, project.sub_track1, project.sub_track2,
                project.year, project.college, project.form_url, project.enclosure_url,
                s.name as cname, sno as cid
            from 
                project join projectstudent p on project.id = p.pid_id join student s on p.sno_id = s.sno
            where 
                `index` = 0 and project.id <> {id} and project.name = '{data["name"]}'
            order by 
                project.name;
        ''')
        same = []
        for s in same_set:
            same.append({
                "id": s.id,
                "name": s.name,
                "main_track": MainTrack.int2str(s.main_track),
                "sub_track1": SubTrack1.int2str(s.sub_track1),
                "sub_track2": SubTrack2.int2str(s.sub_track2),
                "year": s.year,
                "college": College.int2str(s.college),
                "form_url": s.form_url,
                "enclosure_url": s.enclosure_url,
                "cname": s.cname,
                "cid": s.cid
            })
        data["same"] = same
    except Exception as e:
        return JsonResponseUtf8({}, 500, False, str(e))
    return JsonResponseUtf8(data, 200, True, "操作成功")


def getTeachers(request):
    session = request.session.get("info")
    if session is None:
        return JsonResponseUtf8({}, 404, False, "会话过期")
    try:
        teacher_list = Teacher.objects.raw('''
                select 
                    *
                from 
                    teacher;
            ''')
        teachers = []
        for t in teacher_list:
            teachers.append({
                "tno": t.tno,
                "name": t.name,
                "title": t.title,
                "college": College.int2str(t.college),
                "email": t.email,
                "phone": t.phone
            })
    except Exception as e:
        return JsonResponseUtf8({}, 500, False, str(e))
    return JsonResponseUtf8({"teachers": teachers}, 200, True, "操作成功")


def getTeacherDetail(request):
    session = request.session.get("info")
    if session is None:
        return JsonResponseUtf8({}, 404, False, "会话过期")
    try:
        tno = request.GET.get("tno")
        data = {}
        project_list = Project.objects.raw(f'''
            select 
                project.id, project.name, project.main_track, project.sub_track1, project.sub_track2,
                project.year, project.college, project.brief, project.form_url, project.enclosure_url,
                s.name as cname, s.sno as cid
            from 
                project join projectstudent p on project.id = p.pid_id join student s on s.sno = p.sno_id
            where 
                `index` = 0 and project.id in (
                select projectteacher.pid_id from projectteacher where projectteacher.tno_id = '{tno}'
            );
        ''')
        projects = []
        for p in project_list:
            projects.append({
                "id": p.id,
                "name": p.name,
                "main_track": MainTrack.int2str(p.main_track),
                "sub_track1": SubTrack1.int2str(p.sub_track1),
                "sub_track2": SubTrack2.int2str(p.sub_track2),
                "year": p.year,
                "college": College.int2str(p.college),
                "form_url": p.form_url,
                "enclosure_url": p.enclosure_url,
                "cname": p.cname,
                "cid": p.cid
            })
        data["projects"] = projects
        teacher = Teacher.objects.raw(f'''
                select 
                    *
                from 
                    teacher
                where 
                    teacher.tno='{tno}';
            ''')[0]
        data["teacher"] = {
            "tno": teacher.tno,
            "name": teacher.name,
            "title": teacher.title,
            "college": College.int2str(teacher.college),
            "email": teacher.email,
            "phone": teacher.phone,
        }
        same_list = Teacher.objects.raw(f'''
                select 
                    *
                from 
                    teacher
                where 
                    teacher.name='{teacher.name}' and teacher.tno <> '{teacher.tno}';
            ''')
        same = []
        for s in same_list:
            same.append({
                "tno": s.tno,
                "name": s.name,
                "title": s.title,
                "college": College.int2str(s.college),
                "email": s.email,
                "phone": s.phone,
            })
        data["same"] = same
    except Exception as e:
        return JsonResponseUtf8({}, 500, False, str(e))
    return JsonResponseUtf8(data, 200, True, "操作成功")


def getStudents(request):
    session = request.session.get("info")
    if session is None:
        return JsonResponseUtf8({}, 404, False, "会话过期")
    try:
        student_list = Student.objects.raw('''
                select 
                    *
                from 
                    student;
            ''')
        students = []
        for s in student_list:
            students.append({
                "sno": s.sno,
                "name": s.name,
                "sex": Sex.int2str(s.sex),
                "college": College.int2str(s.college),
                "major": s.major,
                "email": s.email,
                "phone": s.phone,
                "qq": s.qq,
                "wechat": s.wechat
            })
    except Exception as e:
        return JsonResponseUtf8({}, 500, False, str(e))
    return JsonResponseUtf8({"students": students}, 200, True, "操作成功")


def getStudentDetail(request):
    session = request.session.get("info")
    if session is None:
        return JsonResponseUtf8({}, 404, False, "会话过期")
    try:
        sno = request.GET.get("sno")
        data = {}
        project_list = Project.objects.raw(f'''
            select 
                project.id, project.name, project.main_track, project.sub_track1, project.sub_track2,
                project.year, project.college, project.brief, project.form_url, project.enclosure_url,
                s.name as cname, s.sno as cid
            from 
                project join projectstudent p on project.id = p.pid_id join student s on s.sno = p.sno_id
            where 
                `index` = 0 and project.id in (
                select projectstudent.pid_id from projectstudent where projectstudent.sno_id = '{sno}'
            );
        ''')
        projects = []
        for p in project_list:
            projects.append({
                "id": p.id,
                "name": p.name,
                "main_track": MainTrack.int2str(p.main_track),
                "sub_track1": SubTrack1.int2str(p.sub_track1),
                "sub_track2": SubTrack2.int2str(p.sub_track2),
                "year": p.year,
                "college": College.int2str(p.college),
                "form_url": p.form_url,
                "enclosure_url": p.enclosure_url,
                "cname": p.cname,
                "cid": p.cid
            })
        data["projects"] = projects
        student = Student.objects.raw(f'''
                select 
                    *
                from 
                    student
                where 
                    student.sno='{sno}';
            ''')[0]
        data["student"] = {
            "sno": student.sno,
            "name": student.name,
            "sex": Sex.int2str(student.sex),
            "college": College.int2str(student.college),
            "major": student.major,
            "email": student.email,
            "phone": student.phone,
            "qq": student.qq,
            "wechat": student.wechat
        }
        same_list = Student.objects.raw(f'''
                select 
                    *
                from 
                    student
                where 
                    student.name='{student.name}' and student.sno <> '{student.sno}';
            ''')
        same = []
        for s in same_list:
            same.append({
                "sno": s.sno,
                "name": s.name,
                "sex": Sex.int2str(s.sex),
                "college": College.int2str(s.college),
                "major": s.major,
                "email": s.email,
                "phone": s.phone,
                "qq": s.qq,
                "wechat": s.wechat
            })
        data["same"] = same
    except Exception as e:
        return JsonResponseUtf8({}, 500, False, str(e))
    return JsonResponseUtf8(data, 200, True, "操作成功")


def getStudentDeleteInfo(request):
    session = request.session.get("info")
    if session is None:
        return JsonResponseUtf8({}, 404, False, "会话过期")
    try:
        sno = request.GET.get("sno")
        data = {"projects": []}
        project_list = Project.objects.raw(f'''
            select 
                project.id, project.name, `index`, division
            from 
                project join projectstudent p on project.id = p.pid_id
            where 
                sno_id = '{sno}';
        ''')
        for p in project_list:
            data["projects"].append({
                "id": p.id,
                "name": p.name,
                "index": p.index,
                "division": p.division,
            })
    except Exception as e:
        return JsonResponseUtf8({}, 500, False, str(e))
    return JsonResponseUtf8(data, 200, True, "操作成功")


def deleteStudent(request):
    if request.method != 'POST':
        return JsonResponseUtf8({}, 405, False, "方法不允许")
    session = request.session.get("info")
    if session is None:
        return JsonResponseUtf8({}, 404, False, "会话过期")
    try:
        sno = request.POST.get("sno")
        with connection.cursor() as cur:
            cur.execute(f'''
                delete from projectstudent where sno_id='{sno}';
            ''')
            cur.execute(f'''
                delete from student where sno='{sno}';
            ''')
            formStudentIndex(cur)
        connection.commit()
    except Exception as e:
        return JsonResponseUtf8({}, 500, False, str(e))
    return JsonResponseUtf8({}, 200, True, "删除成功")


def getTeacherDeleteInfo(request):
    session = request.session.get("info")
    if session is None:
        return JsonResponseUtf8({}, 404, False, "会话过期")
    try:
        tno = request.GET.get("tno")
        data = {"projects": []}
        project_list = Project.objects.raw(f'''
            select 
                project.id, project.name, `index`
            from 
                project join projectteacher p on project.id = p.pid_id
            where 
                tno_id = '{tno}';
        ''')
        for p in project_list:
            data["projects"].append({
                "id": p.id,
                "name": p.name,
                "index": p.index,
            })
    except Exception as e:
        return JsonResponseUtf8({}, 500, False, str(e))
    return JsonResponseUtf8(data, 200, True, "操作成功")


def deleteTeacher(request):
    if request.method != 'POST':
        return JsonResponseUtf8({}, 405, False, "方法不允许")
    session = request.session.get("info")
    if session is None:
        return JsonResponseUtf8({}, 404, False, "会话过期")
    try:
        tno = request.POST.get("tno")
        with connection.cursor() as cur:
            cur.execute(f'''
                delete from projectteacher where tno_id='{tno}';
            ''')
            cur.execute(f'''
                delete from teacher where tno='{tno}';
            ''')
            formTeacherIndex(cur)
        connection.commit()
    except Exception as e:
        return JsonResponseUtf8({}, 500, False, str(e))
    return JsonResponseUtf8({}, 200, True, "删除成功")


def deleteProject(request):
    if request.method != 'POST':
        return JsonResponseUtf8({}, 405, False, "方法不允许")
    session = request.session.get("info")
    if session is None:
        return JsonResponseUtf8({}, 404, False, "会话过期")
    try:
        id = request.POST.get("id")
        with connection.cursor() as cur:
            cur.execute(f'''
                select form_url, enclosure_url from project where id={id};
            ''')
            fa = cur.fetchall()[0]
            form_url = unquote(fa[0])
            enclosure_url = unquote(fa[1])
            delete(form_url)
            delete(enclosure_url)
            cur.execute(f'''
                delete from projectstudent where pid_id={id};
            ''')
            cur.execute(f'''
                delete from projectteacher where pid_id={id};
            ''')
            cur.execute(f'''
                delete from project where id={id};
            ''')
            formStudentIndex(cur)
            formTeacherIndex(cur)
        connection.commit()
    except Exception as e:
        return JsonResponseUtf8({}, 500, False, str(e))
    return JsonResponseUtf8({}, 200, True, "删除成功")


def combineAdd(request):
    if request.method != 'POST':
        return JsonResponseUtf8({}, 405, False, "方法不允许")
    session = request.session.get("info")
    if session is None:
        return JsonResponseUtf8({}, 404, False, "会话过期")
    try:
        with connection.cursor() as cur:
            has = request.POST.get("has")
            if has == "0":
                sno = request.POST.get("no")
                pid = request.POST.get("id")
                index = request.POST.get("index")
                division = request.POST.get("division")
                cur.execute(f'''
                    select 
                        * 
                    from 
                        projectstudent
                    where 
                        sno_id='{sno}' and pid_id={pid}
                ''')
                if cur.fetchall() != ():
                    return JsonResponseUtf8({}, 500, False, "该项目已经与该学生关联")
                cur.execute(f'''
                    update 
                        projectstudent
                    set 
                        `index`=`index`+1
                    where 
                        pid_id={pid} and `index`>={index}
                ''')
                cur.execute(f'''
                    insert into 
                        projectstudent(`index`, division, pid_id, sno_id) 
                    values(
                        {index}, '{division}', {pid}, '{sno}'
                    )
                ''')
            else:
                tno = request.POST.get("no")
                pid = request.POST.get("id")
                index = request.POST.get("index")
                cur.execute(f'''
                    select 
                        * 
                    from 
                        projectteacher
                    where 
                        tno_id='{tno}' and pid_id={pid}
                ''')
                if cur.fetchall() != ():
                    return JsonResponseUtf8({}, 500, False, "该项目已经与该教师关联")
                cur.execute(f'''
                    update 
                        projectteacher
                    set 
                        `index`=`index`+1
                    where 
                        pid_id={pid} and `index`>={index}
                ''')
                cur.execute(f'''
                    insert into 
                        projectteacher(`index`, pid_id, tno_id) 
                    values(
                        {index}, {pid}, '{tno}'
                    )
                ''')
        connection.commit()
    except Exception as e:
        return JsonResponseUtf8({}, 500, False, str(e))
    return JsonResponseUtf8({}, 200, True, "关联成功")


def combineDelete(request):
    if request.method != 'POST':
        return JsonResponseUtf8({}, 405, False, "方法不允许")
    session = request.session.get("info")
    if session is None:
        return JsonResponseUtf8({}, 404, False, "会话过期")
    try:
        with connection.cursor() as cur:
            is_student = request.POST.get("is")
            if is_student == "0":
                sno = request.POST.get("no")
                pid = request.POST.get("id")
                cur.execute(f'''
                    select 
                        * 
                    from 
                        projectstudent
                    where 
                        sno_id='{sno}' and pid_id={pid}
                ''')
                if cur.fetchall() == ():
                    return JsonResponseUtf8({}, 500, False, "该项目已经与该学生解除关联")
                cur.execute(f'''
                    delete from 
                        projectstudent 
                    where 
                        sno_id='{sno}' and pid_id={pid} 
                ''')
                formStudentIndex(cur)
            else:
                tno = request.POST.get("no")
                pid = request.POST.get("id")
                cur.execute(f'''
                    select 
                        * 
                    from 
                        projectteacher
                    where 
                        tno_id='{tno}' and pid_id={pid}
                ''')
                if cur.fetchall() == ():
                    return JsonResponseUtf8({}, 500, False, "该项目已经与该教师解除关联")
                cur.execute(f'''
                    delete from 
                        projectteacher 
                    where 
                        tno_id='{tno}' and pid_id={pid} 
                ''')
                formTeacherIndex(cur)
        connection.commit()
    except Exception as e:
        return JsonResponseUtf8({}, 500, False, str(e))
    return JsonResponseUtf8({}, 200, True, "解除关联成功")


def changeStudent(request):
    if request.method != 'POST':
        return JsonResponseUtf8({}, 405, False, "方法不允许")
    session = request.session.get("info")
    if session is None:
        return JsonResponseUtf8({}, 404, False, "会话过期")
    try:
        edit = request.POST.get("edit")
        sno = request.POST.get("sno")
        name = request.POST.get("name")
        sex = Sex.str2int(request.POST.get("sex"))
        college = College.str2int(request.POST.get("college"))
        major = request.POST.get("major")
        email = request.POST.get("email")
        phone = request.POST.get("phone")
        qq = request.POST.get("qq")
        wechat = request.POST.get("wechat")
        with connection.cursor() as cur:
            if edit == '1':
                cur.execute(f'''
                    select 
                        * 
                    from 
                        student
                    where 
                        sno='{sno}'
                ''')
                if cur.fetchall() != ():
                    return JsonResponseUtf8({}, 500, False, f"{sno}该学生已存在")
                cur.execute(f'''
                    insert into  
                        student(sno, name, sex, college, major, email, phone, qq, wechat)
                    values(
                        '{sno}', '{name}', {sex}, {college}, '{major}', '{email}', '{phone}', '{qq}', '{wechat}'
                    )
                ''')
            else:
                cur.execute(f'''
                    select 
                        * 
                    from 
                        student
                    where 
                        sno='{sno}'
                ''')
                if cur.fetchall() == ():
                    return JsonResponseUtf8({}, 500, False, f"{sno}该学生不存在")
                cur.execute(f'''
                    update 
                        student
                    set 
                        name='{name}',
                        sex={sex},
                        college={college}, 
                        major='{major}', 
                        email='{email}', 
                        phone='{phone}', 
                        qq='{qq}', 
                        wechat='{wechat}'
                    where 
                        sno='{sno}'
                ''')
        connection.commit()
    except Exception as e:
        return JsonResponseUtf8({}, 500, False, str(e))
    return JsonResponseUtf8({}, 200, True, "操作成功")


def changeTeacher(request):
    if request.method != 'POST':
        return JsonResponseUtf8({}, 405, False, "方法不允许")
    session = request.session.get("info")
    if session is None:
        return JsonResponseUtf8({}, 404, False, "会话过期")
    try:
        edit = request.POST.get("edit")
        tno = request.POST.get("tno")
        name = request.POST.get("name")
        college = College.str2int(request.POST.get("college"))
        email = request.POST.get("email")
        phone = request.POST.get("phone")
        title = request.POST.get("title")
        with connection.cursor() as cur:
            if edit == '1':
                cur.execute(f'''
                    select 
                        * 
                    from 
                        teacher
                    where 
                        tno='{tno}'
                ''')
                if cur.fetchall() != ():
                    return JsonResponseUtf8({}, 500, False, f"{tno}该教师已存在")
                cur.execute(f'''
                    insert into  
                        teacher(tno, name, title, college, phone, email)
                    values(
                        '{tno}', '{name}', '{title}', {college}, '{phone}', '{email}'
                    )
                ''')
            else:
                cur.execute(f'''
                    select 
                        * 
                    from 
                        teacher
                    where 
                        tno='{tno}'
                ''')
                if cur.fetchall() == ():
                    return JsonResponseUtf8({}, 500, False, f"{tno}该教师不存在")
                cur.execute(f'''
                    update 
                        teacher
                    set 
                        name='{name}',
                        college={college}, 
                        title='{title}', 
                        email='{email}', 
                        phone='{phone}'
                    where 
                        tno='{tno}'
                ''')
        connection.commit()
    except Exception as e:
        return JsonResponseUtf8({}, 500, False, str(e))
    return JsonResponseUtf8({}, 200, True, "操作成功")


def formStudentIndex(cur):
    cur.execute(f'''
        select distinct pid_id from projectstudent;
    ''')
    pids = cur.fetchall()
    for pid in pids:
        cur.execute(f'''
            select distinct sno_id from projectstudent where pid_id={pid[0]} order by `index`;
        ''')
        snos = cur.fetchall()
        for i in range(len(snos)):
            if i == 0:
                cur.execute(f'''
                    update 
                        projectstudent 
                    set 
                        `index`={i}, division='队长' 
                    where 
                        pid_id={pid[0]} and sno_id='{snos[i][0]}'
                ''')
            else:
                cur.execute(f'''
                    update 
                        projectstudent 
                    set 
                        `index`={i} 
                    where
                        pid_id={pid[0]} and sno_id='{snos[i][0]}'
                ''')


def formTeacherIndex(cur):
    cur.execute(f'''
        select distinct pid_id from projectteacher;
    ''')
    pids = cur.fetchall()
    for pid in pids:
        cur.execute(f'''
            select distinct tno_id from projectteacher where pid_id={pid[0]} order by `index`;
        ''')
        tnos = cur.fetchall()
        for i in range(len(tnos)):
            cur.execute(f'''
                update 
                    projectteacher 
                set 
                    `index`={i} 
                where
                    pid_id={pid[0]} and tno_id='{tnos[i][0]}'
            ''')


def rawSQL(request):
    if request.method != 'POST':
        return JsonResponseUtf8({}, 405, False, "方法不允许")
    session = request.session.get("info")
    if session is None:
        return JsonResponseUtf8({}, 404, False, "会话过期")
    try:
        data = {}
        raw = request.POST.get("SQL")
        print(raw)
        with connection.cursor() as cur:
            cur.execute(raw)
            des = cur.description
            res = cur.fetchall()
            data["column"] = []
            data["len"] = []
            data["res"] = []
            for d in des:
                data["column"].append(d[0])
                data["len"].append(d[2])
            for r in res:
                d = {}
                for lr in range(len(r)):
                    d[data["column"][lr]] = r[lr]
                data["res"].append(d)
    except Exception as e:
        return JsonResponseUtf8({}, 500, False, str(e))
    return JsonResponseUtf8(data, 200, True, "操作成功")


def getBackup(request):
    session = request.session.get("info")
    if session is None:
        return JsonResponseUtf8({}, 404, False, "会话过期")
    try:
        timestamp = request.GET.get('timestamp')

        file_path = f'./backup/'
        file_name = f'backup_{timestamp}.sql'
        file = os.path.join(file_path, file_name)
        print(file)
        response = FileResponse(open(file, "rb"))
        response['Content-Type'] = 'application/octet-stream'
        response['Content-Disposition'] = f'attachment;filename={file_name}'
        file_info = os.stat(file)
        response['Content-Length'] = file_info.st_size
        return response
    except Exception as e:
        print(e)
        return JsonResponseUtf8({}, 500, False, "失败")


def getBackupList(request):
    session = request.session.get("info")
    if session is None:
        return JsonResponseUtf8({}, 404, False, "会话过期")
    try:
        file_list = []
        for file_name in os.listdir('./backup/'):
            timestamp = file_name.split('.')[0].split('_')[1]
            file_list.append({
                'name': file_name,
                'time': time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(float(timestamp))),
                'timestamp': timestamp
            })
        file_list = sorted(file_list, key=itemgetter('timestamp'), reverse=True)
    except Exception as e:
        return JsonResponseUtf8({}, 500, False, str(e))
    return JsonResponseUtf8({"file_list": file_list}, 200, True, '操作成功')


def makeBackup(request):
    session = request.session.get("info")
    if session is None:
        return JsonResponseUtf8({}, 404, False, "会话过期")
    try:
        timestamp = int(datetime.now().timestamp())
        file_path = f'./backup/backup_{timestamp}.sql'
        os.system(f"mysqldump -u root -p20031120 sanhangbei > {file_path}")
    except Exception as e:
        return JsonResponseUtf8({}, 500, False, str(e))
    return JsonResponseUtf8({}, 200, True, '创建备份成功')


def importData(request):
    if request.method != 'POST':
        return JsonResponseUtf8({}, 405, False, "方法不允许")
    session = request.session.get("info")
    if session is None:
        return JsonResponseUtf8({}, 404, False, "会话过期")
    try:
        t = request.POST.get("type")
        file = request.FILES.get("file")
        d_type = {}
        na_values = {}
        teacher_columns = [
            'tno', 'name', 'title', 'college', 'phone', 'email'
        ]
        student_columns = [
            'sno', 'name', 'sex', 'college', 'major', 'email', 'phone', 'qq', 'wechat'
        ]
        if t == '教师':
            for col in teacher_columns:
                d_type[col] = str
                na_values[col] = ''
        else:
            for col in student_columns:
                d_type[col] = str
                na_values[col] = ''
        if file is None:
            return JsonResponseUtf8({}, 500, False, "文件为空")
        if file.name.split(".")[-1] == 'xls' or file.name.split(".")[-1] == 'xlsx':
            excel = pd.read_excel(file, index_col=None, dtype=d_type, na_values=na_values)
        elif file.name.split(".")[-1] == 'csv':
            excel = pd.read_csv(file, index_col=None, dtype=d_type, na_values=na_values)
        else:
            return JsonResponseUtf8({}, 500, False, "文件类型错误")
        columns = excel.columns.values.tolist()

        if t == '教师':
            if columns != teacher_columns:
                return JsonResponseUtf8({}, 500, False, "导入格式错误")
        else:
            if columns != student_columns:
                return JsonResponseUtf8({}, 500, False, "导入格式错误")
        excel = excel.replace(np.nan, '')

        for idx, r in excel.iterrows():
            with connection.cursor() as cur:
                row = {}
                for col in columns:
                    row[col] = str(r[col])
                if t == '教师':
                    cur.execute(f'''
                        select
                            * 
                        from
                            teacher
                        where
                            tno='{row['tno']}'
                    ''')
                    teacher = cur.fetchall()
                    if teacher == ():
                        cur.execute(f'''
                            insert into teacher(tno, name, title, college, phone, email) 
                            values (
                                '{row['tno']}', 
                                '{row['name']}',
                                '{row['title']}',
                                {row['college']}, 
                                '{row['phone']}', 
                                '{row['email']}'
                            )
                        ''')
                    else:
                        teacher = Teacher.objects.filter(tno=row['tno'])[0]
                        cur.execute(f'''
                            update teacher
                            set 
                                name='{teacher.name if row['name'] == '' else row['name']}',
                                title='{teacher.title if row['title'] == '' else row['title']}',
                                college={teacher.college if row['college'] == 0 else row['college']},
                                phone='{teacher.phone if row['phone'] == '' else row['phone']}',
                                email='{teacher.email if row['email'] == '' else row['email']}'
                            where 
                                tno='{row['tno']}'
                            ''')
                else:
                    cur.execute(f'''
                        select
                            * 
                        from
                            student
                        where
                            sno='{row['sno']}'
                    ''')
                    student = cur.fetchall()
                    if student == ():
                        cur.execute(f'''
                            insert into student(sno, name, sex, college, major, email, phone, qq, wechat)
                            values (
                                '{row['sno']}', 
                                '{row['name']}',
                                {row['sex']},
                                {row['college']}, 
                                '{row['major']}', 
                                '{row['email']}',
                                '{row['phone']}',
                                '{row['qq']}',
                                '{row['wechat']}'
                            )
                        ''')
                    else:
                        student = Student.objects.filter(sno=row['sno'])[0]
                        cur.execute(f'''
                            update student
                            set 
                                name='{student.name if row['name'] == '' else row['name']}',
                                sex={student.sex if row['sex'] == 2 else row['sex']},
                                college={student.college if row['college'] == 0 else row['college']},
                                major='{student.major if row['major'] == '' else row['major']}',
                                email='{student.email if row['email'] == '' else row['email']}',
                                phone='{student.phone if row['phone'] == '' else row['phone']}',
                                qq='{student.qq if row['qq'] == '' else row['qq']}',
                                wechat='{student.wechat if row['wechat'] == '' else row['wechat']}'
                            where 
                                sno='{row['sno']}'
                        ''')
            connection.commit()
    except Exception as e:
        print(e.__traceback__)
        return JsonResponseUtf8({}, 500, False, str(e))
    return JsonResponseUtf8({}, 200, True, '导入成功')


def JsonResponseUtf8(data, code: int, success: bool, message: str) -> HttpResponse:
    rtn = {
        "data": data,
        "code": code,
        "success": success,
        "message": message
    }
    return HttpResponse(
        json.dumps(rtn, ensure_ascii=False, cls=ComplexEncoder),
        content_type="application/json,charset=utf-8"
    )
